﻿Imports ComponentFactory.Krypton.Toolkit
Imports System.Data.OleDb
Imports System.Runtime.InteropServices
Public Class ConsultaFuncionario
    Inherits KryptonForm

    Private conexao As OleDbConnection
    Private command As OleDbCommand
    Private dataSet As DataSet
    Private dataView As DataView
    Private dataAdapter As OleDbDataAdapter
    Private cmdBuilder As OleDbCommandBuilder
    Private strQueryFuncionario As String = "SELECT codFuncionario " & _
                                        ", nomeFuncionario " & _
                                        ", sexoFuncionario " & _
                                        ", cpfFuncionario " & _
                                        ", rgFuncionario  " & _
                                        ", dtnascFuncionario  " & _
                                        ", estadocivilFuncionario  " & _
                                        ", enderecoFuncionario   " & _
                                        ", numenderecoFuncionario   " & _
                                        ", tblTipoLogradouro.descricaotipoLogradouro   " & _
                                        ", complementoFuncionario " & _
                                        ", bairroFuncionario " & _
                                        ", cepFuncionario " & _
                                        ", tblEstado.siglaEstado" & _
                                        ", cidadeFuncionario " & _
                                        ", emailFuncionario " & _
                                        ", salarioFuncionario " & _
                                        ", cargo " & _
                                        ", dtadmissao " & _
                                        " FROM ((tblFuncionario" & _
                                        " LEFT JOIN tblTipoLogradouro ON tblTipoLogradouro.codtipoLogradouro = tblFuncionario.codlogradouroFuncionario)" & _
                                        " LEFT JOIN tblEstado ON tblEstado.codEstado = tblFuncionario.codEstado)"

    Private strTipoFiltro = Nothing
    Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.

        strTipoFiltro = "Proprietario like '%{0}%'"

        cbfiltro.SelectedIndex = 0

        conexao = FabricaDeConexoes.CriarConexao()
        dataSet = New DataSet()

        carregarFuncionario()
    End Sub

    Private Sub carregarFuncionario()
        Try
            If (dataSet IsNot Nothing) Then
                dataAdapter = New OleDbDataAdapter(strQueryFuncionario, conexao)
                dataSet = New DataSet()
                dataAdapter.Fill(dataSet, "tblFuncionario")

                dataView = New DataView(dataSet.Tables("tblFuncionario"), Nothing, "codFuncionario", DataViewRowState.CurrentRows)

                dtgFuncionario.DataSource = dataView
                dtgFuncionario.Columns(0).Visible = False
                dtgFuncionario.Columns(1).HeaderText = "Nome"
                dtgFuncionario.Columns(2).HeaderText = "Sexo"
                dtgFuncionario.Columns(3).HeaderText = "CPF"
                dtgFuncionario.Columns(4).HeaderText = "RG"
                dtgFuncionario.Columns(5).HeaderText = "Data Nascimento"
                dtgFuncionario.Columns(6).HeaderText = "Estado Civil"
                dtgFuncionario.Columns(7).HeaderText = "Endereco"
                dtgFuncionario.Columns(8).HeaderText = "Número"
                dtgFuncionario.Columns(9).HeaderText = "Logradouro"
                dtgFuncionario.Columns(10).HeaderText = "Complemento"
                dtgFuncionario.Columns(11).HeaderText = "Bairro"
                dtgFuncionario.Columns(12).HeaderText = "CEP"
                dtgFuncionario.Columns(13).HeaderText = "Estado"
                dtgFuncionario.Columns(14).HeaderText = "Cidade"
                dtgFuncionario.Columns(15).HeaderText = "Email"
                dtgFuncionario.Columns(16).HeaderText = "Salario"
                dtgFuncionario.Columns(17).HeaderText = "Cargo"
                dtgFuncionario.Columns(18).HeaderText = "Data"
            End If
        Catch ex As Exception
            Debug.WriteLine(ex)
        End Try
    End Sub

    Private Sub btnFechar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFechar.Click
        Me.Dispose()
    End Sub

    Private Sub btnNovo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNovo.Click
        Try
            Dim frmcad As New CadastroFuncionario()
            frmcad.ShowDialog()
            carregarFuncionario()
        Catch ex As Exception
            Debug.WriteLine(ex.StackTrace)
        End Try
    End Sub

    Private Sub cbtipopesquisa_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbfiltro.SelectedIndexChanged
        txtFiltrar.Clear()
        txtFiltrar.Focus()

        Select Case (cbfiltro.SelectedItem.ToString())

            Case "nomeFuncionario"
                strTipoFiltro = "Nome like '%{0}%'"
            Case "cpfFuncionario"
                strTipoFiltro = "CPF like '%{0}%'"
            Case "cargo"
                strTipoFiltro = " Cargo like '%{0}%'"

            Case Else
                strTipoFiltro = Nothing

        End Select
    End Sub

    Private Sub txtfiltrar_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFiltrar.TextChanged
        dataView.RowFilter = String.Format(strTipoFiltro, txtfiltrar.Text)
    End Sub

    Private Sub btnExcluir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcluir.Click
        KryptonMessageBox.Show("Antes de excluir, verifique os dados do funcionário.", "Atenção", MessageBoxButtons.OK, MessageBoxIcon.Information)
        btnImprimir.PerformClick()

        Dim resp = KryptonMessageBox.Show("Tem certeza que deseja excluir este cadastro?" & vbNewLine & "As alterações causadas pela exclusão são irreversíveis.", "Aviso", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If (resp = Windows.Forms.DialogResult.Yes) Then
            Excluirfuncionario()
        End If
    End Sub

    Private Sub btnEditar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditar.Click
        Dim frm As New CadastroFuncionario(dtgFuncionario.CurrentRow.Cells.Item(0).Value)
        frm.ShowDialog()
        Dim cmdBuilder As New OleDbCommandBuilder(dataAdapter)
        dataAdapter.Update(dataSet, "tblFuncionario")
        carregarFuncionario()
    End Sub
    Public Sub Excluirfuncionario()
        Try
            'Dim queryAuto As String = "DELETE * FROM tblAuto WHERE tblAuto.codCliente = " & DataGridView.CurrentRow.Cells(0).Value
            Dim queryTelefone As String = "DELETE * FROM tblTelefoneFuncionario WHERE tblTelefoneFuncionario.codFuncionario = " & dtgFuncionario.CurrentRow.Cells(0).Value
            Dim queryFuncionario As String = "DELETE FROM tblFuncionario WHERE codFuncionario = " & dtgFuncionario.CurrentRow.Cells.Item("codFuncionario").Value

            conexao.Open()

            'Exclui os telefones do cliente
            command = New OleDbCommand(queryTelefone, conexao)
            command.CommandText = queryTelefone
            command.ExecuteNonQuery()

            'Exclui o registro do cliente

            command.CommandText = queryFuncionario
            command.ExecuteNonQuery()

            'Atualiza o DataGridView
            dtgFuncionario.Rows.Remove(dtgFuncionario.CurrentRow)

        Catch ex As OleDbException
            Debug.WriteLine("Erro: " & ex.Message)
        Finally
            command.Dispose()
            conexao.Close()
        End Try
    End Sub

    Private Sub btnImprimir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImprimir.Click

        Dim cod = dtgFuncionario.CurrentRow.Cells(0).Value
        Dim query = "SELECT tblFuncionario.*,tblEstado.descricaoEstado FROM (tblFuncionario INNER JOIN tblEstado ON tblFuncionario.codEstado = tblEstado.codEstado) WHERE tblFuncionario.codFuncionario = " & cod

        Dim ds As New DataSet()
        Dim da As New OleDbDataAdapter(query, conexao)
        da.Fill(ds, "tblFuncionario")
        da.SelectCommand.CommandText = "SELECT * FROM tblTelefoneFuncionario WHERE codFuncionario = " & cod
        da.Fill(ds, "tblTelefoneFuncionario")

        Dim doc As New CrystalDecisions.CrystalReports.Engine.ReportDocument()
        doc.Load(Config.DiretorioAplicacao & "/../../../Relatorio/relatorioIndivFunc.rpt", CrystalDecisions.Shared.OpenReportMethod.OpenReportByDefault)
        doc.SetDataSource(ds)
        Dim frm As New VisualizadorAvancado()
        frm.setDocument(doc)
        frm.ShowDialog()

    End Sub
End Class